﻿Imports DataAccess
Imports DTO
Public Class BSWorkData

    Public strColNo As String = "Employee No"
    Public strColName As String = "Employee Name"
    Public strColOrganName As String = "Organization"
    Public strColOrganNo As String = "organNo"
    Public strColExFrom As String = "exFrom"
    Public strColExTo As String = "exTo"

    Dim serverName As String

    Public Sub New(ByVal serverName As String)

        ' config server name
        Me.serverName = serverName

        DATimetable.ConfigServerName(serverName)

    End Sub

    ''' <summary>
    ''' update start time and end time
    ''' </summary>
    ''' <param name="employeeNo"></param>
    ''' <param name="workDate"></param>
    ''' <param name="startTime"></param>
    ''' <param name="endTime"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateStartTimeToWorkData(ByVal employeeNo As String, ByVal workDate As Integer, ByVal startTime As Integer,
                                              ByVal endTime As Integer) As Integer

        Dim dtoWorkData As New DTOWorkData
        dtoWorkData.EmployeeNoProperty = employeeNo
        dtoWorkData.WorkDateProperty = workDate
        dtoWorkData.StartTimeProperty = startTime
        dtoWorkData.EndTimeProperty = endTime

        'update start Time, end Time
        DAWorkData.UpdateTimeToWorkData(dtoWorkData)

        Return UpdateCalculateTimeToWorkData(employeeNo, workDate)

    End Function


    ''' <summary>
    ''' update calculate time
    ''' </summary>
    ''' <param name="employeeNo"></param>
    ''' <param name="workDate"></param>
    ''' <param name="startTime"></param>
    ''' <param name="endTime"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateCalculateTimeToWorkData(ByVal employeeNo As String, ByVal workDate As Integer) As Integer

        Dim dtWorkData As DataTable = GetWorkData(employeeNo, workDate, workDate)

        Dim startTime As Integer

        Dim endTime As Integer

        If Not IsDBNull(dtWorkData.Rows.Item(0).Item("START_TIME")) Then

            startTime = Double.Parse(dtWorkData.Rows.Item(0).Item("START_TIME"))

        Else

            startTime = 0

        End If


        If Not IsDBNull(dtWorkData.Rows.Item(0).Item("END_TIME")) Then

            endTime = Double.Parse(dtWorkData.Rows.Item(0).Item("END_TIME"))

        Else

            endTime = 0

        End If


        '--calculate details
        'get timetable 
        Dim dtTimetable As DataTable = GetTimetableFromWorkDate(employeeNo, workDate)

        Dim workTime As Object = 0

        Dim overtime As Object = 0

        Dim leaveEarly As Object = 0

        Dim lateness As Object = 0

        Dim onDutyA As Object = 0

        Dim offDutyA As Object = 0

        Dim restTime As Object = 0

        If startTime > 0 And endTime > 0 Then

            'round up start time
            onDutyA = RoundOnDutyUp(startTime, dtTimetable.Rows.Item(0).Item("WORK_FROM"),
                                                   dtTimetable.Rows.Item(0).Item("UNIT_MINUTES"),
                                                   dtTimetable.Rows.Item(0).Item("OVER_UNIT_MINUTES"))

            offDutyA = RoundOffDutyDown(endTime, dtTimetable.Rows.Item(0).Item("WORK_TO"),
                                                       dtTimetable.Rows.Item(0).Item("UNIT_MINUTES"),
                                                       dtTimetable.Rows.Item(0).Item("OVER_UNIT_MINUTES"))


            ' get rest times
            Dim dtoTimetable As DTOTimetable = New DTOTimetable()

            dtoTimetable.TimetableNoProperty = dtTimetable.Rows.Item(0).Item("TIME_TABLE_NO")

            dtoTimetable.ExFromProperty = dtTimetable.Rows.Item(0).Item("EXPIRATION_FROM")

            dtoTimetable.ExToProperty = dtTimetable.Rows.Item(0).Item("EXPIRATION_TO")

            Dim dtRestTimes As Data.DataTable

            Dim bsTimetable As BSTimetable = New BSTimetable(serverName)

            dtRestTimes = bsTimetable.GetRestTime(dtoTimetable)

            ' calculate rest time
            restTime = CalculateRestTime(onDutyA, offDutyA, dtRestTimes)

            FigureTime(onDutyA, offDutyA, dtTimetable.Rows.Item(0).Item("WORK_FROM"), dtTimetable.Rows.Item(0).Item("WORK_TO"),
                       restTime, dtRestTimes, workTime, overtime, leaveEarly, lateness)

        ElseIf startTime > 0 And endTime <= 0 Then

            'round up start time
            onDutyA = RoundOnDutyUp(startTime, dtTimetable.Rows.Item(0).Item("WORK_FROM"),
                                                   dtTimetable.Rows.Item(0).Item("UNIT_MINUTES"),
                                                   dtTimetable.Rows.Item(0).Item("OVER_UNIT_MINUTES"))

            If onDutyA > dtTimetable.Rows.Item(0).Item("WORK_FROM") Then

                lateness = Business.Functions.SubtractHourReturnHour(onDutyA,
                                                                     dtTimetable.Rows.Item(0).Item("WORK_FROM"))

            End If

        ElseIf startTime <= 0 And endTime > 0 Then

            offDutyA = RoundOffDutyDown(endTime, dtTimetable.Rows.Item(0).Item("WORK_TO"),
                                                       dtTimetable.Rows.Item(0).Item("UNIT_MINUTES"),
                                                       dtTimetable.Rows.Item(0).Item("OVER_UNIT_MINUTES"))

            If offDutyA < dtTimetable.Rows.Item(0).Item("WORK_TO") Then

                leaveEarly = Business.Functions.SubtractHourReturnHour(dtTimetable.Rows.Item(0).Item("WORK_TO"), offDutyA)

            End If

        End If

        ' check duty type
        Dim workDays As Double = 1


        If onDutyA.ToString().Equals("0") Then

            onDutyA = DBNull.Value

        End If

        If offDutyA.ToString().Equals("0") Then

            offDutyA = DBNull.Value

        End If

        If restTime.ToString().Equals("0") Then

            restTime = DBNull.Value

        End If

        If workTime.ToString().Equals("0") Then

            workTime = DBNull.Value

        End If

        If overtime.ToString().Equals("0") Then

            overtime = DBNull.Value

        End If

        If lateness.ToString().Equals("0") Then

            lateness = DBNull.Value

        End If

        If leaveEarly.ToString().Equals("0") Then

            leaveEarly = DBNull.Value

        End If

        If Not workTime Is DBNull.Value Then

            workTime = Functions.ConvertHourToMinutes(workTime)

        End If

        If Not overtime Is DBNull.Value Then

            overtime = Functions.ConvertHourToMinutes(overtime)

        End If

        If Not lateness Is DBNull.Value Then

            lateness = Functions.ConvertHourToMinutes(lateness)

        End If

        If Not leaveEarly Is DBNull.Value Then

            leaveEarly = Functions.ConvertHourToMinutes(leaveEarly)

        End If

        If Not restTime Is DBNull.Value Then

            restTime = Functions.ConvertHourToMinutes(restTime)

        End If

        Return DAWorkData.UpdateCalculateTimeToWorkData(onDutyA, offDutyA, employeeNo, workDate, workTime,
                                                        overtime, lateness, leaveEarly, workDays, restTime)

    End Function


    ''' <summary>
    ''' get timetable in date
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetTimetableFromWorkDate(ByVal employeeNo As String, ByVal workDate As Integer) As DataTable

        Return DAWorkData.GetTimetableFromWorkData(employeeNo, workDate)

    End Function

    ''' <summary>
    ''' Get all employee in work data in a month, dfrom and dto format yyyyMMdd
    ''' </summary>
    ''' <param name="organNo"></param>
    ''' <param name="strSearch"></param>
    ''' <param name="dFrom"></param>
    ''' <param name="dTo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetAllEmployeeWorkData(ByVal organNo As String, ByVal strSearch As String, ByVal dFrom As String,
                                           ByVal dTo As String) As DataTable

        Return DAWorkData.GetAllEmployeeWorkData(organNo, strSearch, dFrom, dTo)


    End Function



    ''' <summary>
    ''' get cut off date
    ''' </summary>
    ''' <param name="organNo"></param>
    ''' <param name="month"></param>
    ''' <param name="year"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetCutOffDate(ByVal organNo As String, ByVal month As Integer, ByVal year As Integer) As Integer

        ' get monthly of a organization
        Dim dtMonthly As DataTable = DAMonthly.GetMonthly(organNo)

        'merge month and year
        Dim monthYear As Integer = year * 100 + month

        ' check which monthly is applying at that time
        For Each row As DataRow In dtMonthly.Rows

            If Integer.Parse(row.Item("EXPIRATION_FROM").ToString()) <= monthYear And Integer.Parse(row.Item("EXPIRATION_TO").ToString()) >= monthYear Then

                Return Integer.Parse(row.Item("CUT_OFF_DATE"))

            End If

        Next

    End Function



    ''' <summary>
    ''' Get start date and end date in a particular month
    ''' </summary>
    ''' <param name="organNo"></param>
    ''' <param name="month"></param>
    ''' <param name="year"></param>
    ''' <param name="startDate"></param>
    ''' <param name="endDate"></param>
    ''' <remarks></remarks>
    Public Sub GetStartEndDateOfMonth(ByVal organNo As String, ByVal month As Integer, ByVal year As Integer, ByRef startDate As DateTime, ByRef endDate As DateTime)

        ' get the first date of current month
        Dim dCMonth As DateTime = New Date(year, month, 1)

        'get the last date of previous month
        Dim dPMonth As DateTime = dCMonth.AddDays(-1)

        'get cut off date of previous month
        Dim preCutOffDate As Integer = GetCutOffDate(organNo, dPMonth.Month, dPMonth.Year)

        'get cut off date of current month
        Dim curCutOffDate As Integer = GetCutOffDate(organNo, dCMonth.Month, dCMonth.Year)

        ' get number of days in previous month
        Dim preNumOfDays As Integer = Date.DaysInMonth(dPMonth.Year, dPMonth.Month)

        ' start date 
        If (preCutOffDate + 1) <= preNumOfDays Then

            startDate = New Date(dPMonth.Year, dPMonth.Month, preCutOffDate + 1)

        Else

            startDate = New Date(dCMonth.Year, dCMonth.Month, 1)

        End If

        'get number of days in current month
        Dim curNumOfDays As Integer = Date.DaysInMonth(dCMonth.Year, dCMonth.Month)

        'end date
        If curCutOffDate <= curNumOfDays Then

            endDate = New Date(dCMonth.Year, dCMonth.Month, curCutOffDate)

        Else

            ' get the last day in current month
            Dim dLast As DateTime = New Date(dCMonth.Year, dCMonth.Month, curNumOfDays)

            endDate = dLast

        End If

    End Sub


    ''' <summary>
    ''' Return Datatable employee change name data column
    ''' </summary>
    ''' <param name="dtEmployee"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetDataSourceEmpWork(ByVal dtEmployee As DataTable) As DataTable

        Dim dataTable As DataTable = New DataTable()


        dataTable.Columns.Add(strColOrganName)
        dataTable.Columns.Add(strColNo)
        dataTable.Columns.Add(strColName)
        dataTable.Columns.Add(strColOrganNo)
        dataTable.Columns.Add(strColExFrom)
        dataTable.Columns.Add(strColExTo)

        Dim newRow As DataRow

        For Each row As DataRow In dtEmployee.Rows

            newRow = dataTable.NewRow()
            newRow.Item(strColNo) = row.Item("EMPLOYEE_NO")
            newRow.Item(strColName) = row.Item("LAST_NAME").ToString() & " " & row.Item("FIRST_NAME").ToString()
            newRow.Item(strColOrganName) = row.Item("ORGANIZATIONAL_NAME")
            newRow.Item(strColOrganNo) = row.Item("ORGANIZATIONAL_NO")
            newRow.Item(strColExFrom) = row.Item("EXPIRATION_FROM")
            newRow.Item(strColExTo) = row.Item("EXPIRATION_TO")

            dataTable.Rows.Add(newRow)

        Next

        Return dataTable

    End Function


    ''' <summary>
    ''' Round onduty up according to Timetable start time, unit minutes and overtime unit minutes
    ''' </summary>
    ''' <param name="onDuty">hhmm</param>
    ''' <param name="startTime">hhmm</param>
    ''' <param name="unitMinutes">mm</param>
    ''' <param name="oTUnitMinutes">mm</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function RoundOnDutyUp(ByVal onDuty As Integer, ByVal startTime As Integer, ByVal unitMinutes As Integer,
                             ByVal oTUnitMinutes As Integer) As Integer

        ' if employee arrives earlier than start time, round onduty according to overtime unit minutes
        If onDuty < startTime Then

            ' get landmark begin overtime
            Dim hMarkStartOT As Integer = Functions.SubtractHourToMinute(startTime, oTUnitMinutes)

            ' if on duty is in period of time beginning calculate overtime
            If onDuty <= hMarkStartOT Then

                ' to calculate periof of time overtime
                Dim mPeriodOT As Integer = Functions.SubtractHourToHour(hMarkStartOT, onDuty)

                If mPeriodOT < oTUnitMinutes Then

                    Return hMarkStartOT

                Else

                    ' round period of time overtime up
                    mPeriodOT = (mPeriodOT \ oTUnitMinutes) * oTUnitMinutes

                    Return Functions.SubtractHourToMinute(hMarkStartOT, mPeriodOT)

                End If

            Else

                Return startTime

            End If

        Else

            ' employee arrives late, round onduty according to unit minutes
            ' calculate period of time late
            Dim mPeriodLate As Integer = Functions.SubtractHourToHour(onDuty, startTime)

            Dim int As Integer = mPeriodLate \ unitMinutes

            Dim remainder As Integer = mPeriodLate Mod unitMinutes

            ' round period of time late 
            Dim mRoundPeriodLate As Integer = 0

            For i As Integer = 0 To int - 1

                mRoundPeriodLate = mRoundPeriodLate + unitMinutes

            Next

            If remainder <> 0 Then

                mRoundPeriodLate = mRoundPeriodLate + unitMinutes

            End If

            Return Functions.AddHour(startTime, (mRoundPeriodLate \ 60) * 100 + (mRoundPeriodLate Mod 60))

        End If

    End Function


    ''' <summary>
    ''' Round off duty down according to Timetable start time, unit minutes and overtime unit minutes
    ''' </summary>
    ''' <param name="offDuty"></param>
    ''' <param name="endTime"></param>
    ''' <param name="unitMinutes"></param>
    ''' <param name="oTUnitMinutes"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function RoundOffDutyDown(ByVal offDuty As Integer, ByVal endTime As Integer, ByVal unitMinutes As Integer,
                                     ByVal oTUnitMinutes As Integer) As Integer

        ' employee left late than end time. round off duty according to overtime unit minutes
        If offDuty > endTime Then

            ' get landmark begin overtime
            Dim hMarkStartOT As Integer = Functions.AddHoursAndMinutes(endTime, oTUnitMinutes)

            ' if off duty is in period of time beginning calculate overtime
            If offDuty >= hMarkStartOT Then
                
                ' to calculate periof of time overtime
                Dim mPeriodOT As Integer = Functions.SubtractHourToHour(offDuty, hMarkStartOT)

                If mPeriodOT < oTUnitMinutes Then

                    Return hMarkStartOT

                Else

                    ' round period of time overtime up
                    mPeriodOT = (mPeriodOT \ oTUnitMinutes) * oTUnitMinutes

                    Return Functions.AddHoursAndMinutes(hMarkStartOT, mPeriodOT)

                End If

            Else

                Return endTime

            End If

        Else

            'employee leave early, round off duty according to unit minutes
            ' calculate period of time leave early
            Dim mPeriodLeaveEarly As Integer = Functions.SubtractHourToHour(endTime, offDuty)

            Dim int As Integer = mPeriodLeaveEarly \ unitMinutes

            Dim remainder As Integer = mPeriodLeaveEarly Mod unitMinutes

            ' round period of time late 
            Dim mRoundPeriodLeaveEarly As Integer = 0

            For i As Integer = 0 To int - 1

                mRoundPeriodLeaveEarly = mRoundPeriodLeaveEarly + unitMinutes

            Next

            If remainder <> 0 Then

                mRoundPeriodLeaveEarly = mRoundPeriodLeaveEarly + unitMinutes

            End If

            Return Functions.SubtractHourToMinute(endTime, mRoundPeriodLeaveEarly)

        End If
        
    End Function

    ''' <summary>
    ''' Calculate rest time, from begin time to end time, return hour hhmm
    ''' </summary>
    ''' <param name="beginTime"></param>
    ''' <param name="endTime"></param>
    ''' <param name="RestTimes">key: rest from, value: rest to</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CalculateRestTime(ByVal beginTime As Integer, ByVal endTime As Integer, ByVal restTimes As DataTable) As Integer

        Dim rest As Integer = 0

        Dim restFrom As Integer

        Dim restTo As Integer

        For Each row As DataRow In restTimes.Rows

            restFrom = Integer.Parse(row.Item("REST_FROM"))

            restTo = Integer.Parse(row.Item("REST_TO"))

            If restFrom > beginTime And restTo < endTime Then

                rest = Functions.AddHour(Functions.SubtractHourReturnHour(restTo, restFrom), rest)

            ElseIf restFrom > beginTime And restFrom < endTime And restTo > endTime Then

                rest = Functions.AddHour(Functions.SubtractHourReturnHour(endTime, restFrom), rest)

            ElseIf restFrom < beginTime And restTo > beginTime And restTo < endTime Then

                rest = Functions.AddHour(Functions.SubtractHourReturnHour(restTo, beginTime), rest)
                
            End If

        Next

        Return rest

    End Function

    ''' <summary>
    ''' To figure out working time, overtime, leave early, lateness.
    ''' rest: rest time from ondutyA to offdutyA.
    ''' </summary>
    ''' <param name="onDutyA">hhmm</param>
    ''' <param name="offDutyA">hhmm</param>
    ''' <param name="startTime">hhmm</param>
    ''' <param name="endTime">hhmm</param>
    ''' <param name="rest">rest time from ondutyA to offdutyA</param>
    ''' <param name="restTimes"></param>
    ''' <param name="workTime">hhmm</param>
    ''' <param name="overTime">hhmm</param>
    ''' <param name="leaveEarly">hhmm</param>
    ''' <param name="lateness">hhmm</param>
    ''' <remarks></remarks>
    Public Sub FigureTime(ByVal onDutyA As Integer, ByVal offDutyA As Integer, ByVal startTime As Integer, ByVal endTime As Integer,
                          ByVal rest As Integer, ByVal restTimes As DataTable, ByRef workTime As Integer, ByRef overTime As Integer,
                          ByRef leaveEarly As Integer, ByRef lateness As Integer)

        ' employee start work on time
        If onDutyA = startTime Then

            ' start work on time and leave on time
            If offDutyA = endTime Then

                workTime = Functions.SubtractHourReturnHour(Functions.SubtractHourReturnHour(endTime, startTime), rest)

                ' start work on time and leave early
            ElseIf offDutyA < endTime Then

                workTime = Functions.SubtractHourReturnHour(Functions.SubtractHourReturnHour(offDutyA, startTime), rest)

                leaveEarly = Functions.SubtractHourReturnHour(endTime, offDutyA)

                ' start work on time and leave late -> overtime
            Else

                ' calculate rest in working time
                Dim restInWork As Integer = CalculateRestTime(startTime, endTime, restTimes)

                workTime = Functions.SubtractHourReturnHour(Functions.SubtractHourReturnHour(endTime, startTime), restInWork)

                ' calculate rest in overtime
                Dim restInOT As Integer = SubtractHourReturnHour(rest, restInWork)

                If restInOT > 0 Then

                    overTime = Functions.SubtractHourReturnHour(Functions.SubtractHourReturnHour(offDutyA, endTime), restInOT)

                Else

                    overTime = Functions.SubtractHourReturnHour(offDutyA, endTime)

                End If


            End If

            ' start work late
        ElseIf onDutyA > startTime Then

            ' calculate lateness
            ' calculate rest in period of time lateness
            Dim restInLateness As Integer = CalculateRestTime(startTime, onDutyA, restTimes)

            lateness = Functions.SubtractHourReturnHour(Functions.SubtractHourReturnHour(onDutyA, startTime), restInLateness)

            ' start work late and leave on time
            If offDutyA = endTime Then

                workTime = Functions.SubtractHourReturnHour(Functions.SubtractHourReturnHour(endTime, onDutyA), rest)

                ' start work late and leave early
            ElseIf offDutyA < endTime Then

                workTime = Functions.SubtractHourReturnHour(Functions.SubtractHourReturnHour(offDutyA, onDutyA), rest)

                ' leave early
                leaveEarly = Functions.SubtractHourReturnHour(endTime, offDutyA)

                ' start work late and leave late
            Else

                ' calculate rest in working time
                Dim restInWork As Integer = CalculateRestTime(onDutyA, endTime, restTimes)

                workTime = Functions.SubtractHourReturnHour(Functions.SubtractHourReturnHour(endTime, onDutyA), restInWork)

                ' calculate rest in overtime

                Dim restInOT As Integer = Functions.SubtractHourReturnHour(rest, restInWork)

                If restInOT > 0 Then

                    overTime = Functions.SubtractHourReturnHour(Functions.SubtractHourReturnHour(offDutyA, endTime), restInOT)

                Else

                    overTime = Functions.SubtractHourReturnHour(offDutyA, endTime)

                End If

            End If

            'start work early ondutyA < startTime
        Else

            ' start work early and leave on time
            If offDutyA = endTime Then

                ' calculate rest in working time
                Dim restInWork As Integer = CalculateRestTime(startTime, endTime, restTimes)

                workTime = Functions.SubtractHourReturnHour(Functions.SubtractHourReturnHour(endTime, startTime), restInWork)

                ' calculate rest in overtime
                Dim restInOT As Integer = Functions.SubtractHourReturnHour(rest, restInWork)

                If restInOT > 0 Then

                    overTime = Functions.SubtractHourReturnHour(Functions.SubtractHourReturnHour(startTime, onDutyA), restInOT)

                Else

                    overTime = Functions.SubtractHourReturnHour(startTime, onDutyA)

                End If

                ' start work early and leave early
            ElseIf offDutyA < endTime Then

                ' calculate rest in working time
                Dim restInWork As Integer = CalculateRestTime(startTime, offDutyA, restTimes)

                workTime = Functions.SubtractHourReturnHour(Functions.SubtractHourReturnHour(offDutyA, startTime), restInWork)

                ' calculate leave early
                leaveEarly = Functions.SubtractHourReturnHour(endTime, offDutyA)

                ' calculate rest in overtime
                Dim restInOT As Integer = Functions.SubtractHourReturnHour(rest, restInWork)

                If restInOT > 0 Then

                    overTime = Functions.SubtractHourReturnHour(Functions.SubtractHourReturnHour(startTime, onDutyA), restInOT)

                Else

                    overTime = Functions.SubtractHourReturnHour(startTime, onDutyA)

                End If

                ' start work early and leave late, offduty > endtime
            Else

                ' calculate rest in working time
                Dim restInWork As Integer = CalculateRestTime(startTime, endTime, restTimes)

                workTime = Functions.SubtractHourReturnHour(Functions.SubtractHourReturnHour(endTime, startTime), restInWork)

                ' calculate rest in overtime
                Dim restInOT As Integer = Functions.SubtractHourReturnHour(rest, restInWork)

                If restInOT > 0 Then

                    overTime = Functions.SubtractHourReturnHour(Functions.SubtractHourReturnHour(startTime, onDutyA), restInOT)

                Else

                    overTime = Functions.SubtractHourReturnHour(startTime, onDutyA)

                End If

            End If

        End If


    End Sub


    ''' <summary>
    ''' Get work data of an employee in period of time, yyyyMMdd 
    ''' </summary>
    ''' <param name="organNo"></param>
    ''' <param name="empNo"></param>
    ''' <param name="dayFrom">yyyyMMdd</param>
    ''' <param name="dayTo">yyyyMMdd</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkData(ByVal organNo As String, ByVal empNo As String, ByVal dayFrom As String, ByVal dayTo As String) As DataTable

        Return DAWorkData.GetWorkData(organNo, empNo, dayFrom, dayTo)

    End Function


    ''' <summary>
    '''  Get work data of an employee in period of time, yyyyMMdd 
    ''' </summary>
    ''' <param name="empNo"></param>
    ''' <param name="dayFrom"></param>
    ''' <param name="dayTo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkData(ByVal empNo As String, ByVal dayFrom As String, ByVal dayTo As String) As DataTable

        Return DAWorkData.GetWorkData(empNo, dayFrom, dayTo)

    End Function
 
    ''' <summary>
    ''' insert work date of employee into database
    ''' </summary>
    ''' <param name="organNo"></param>
    ''' <param name="empNo"></param>
    ''' <param name="startDate"></param>
    ''' <param name="endDate"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function InsertWorkDate(ByVal organNo As String, ByVal empNo As String, ByVal exFrom As String, ByVal exTo As String,
                                   ByVal startDate As DateTime, ByVal endDate As DateTime) As Boolean

        ' start date and end date in one month
        If startDate.Month = endDate.Month Then

            For i As Integer = startDate.Day To endDate.Day

                Dim day As String = startDate.ToString("yyyy") & startDate.ToString("MM") & i.ToString("00")

                Dim workContract As Integer

                Dim dtTimetable As DataTable = GetTimetableEmployee(empNo, exFrom, exTo, workContract)

                'check if date is holiday and work type <> 8,9
                If New BSHoliday(serverName).CheckHoliday(organNo, day) Then

                    workContract = 0

                End If

                DAWorkData.InsertWorkDate(organNo, empNo, exFrom, exTo, day, dtTimetable, workContract)

            Next

        Else
            ' start date and end date in two months
            ' Get number of days in the month containing start date
            Dim numOfDate As Integer = Date.DaysInMonth(startDate.Year, startDate.Month)

            ' get last date in this month
            Dim lastDate As Date = New Date(startDate.Year, startDate.Month, numOfDate)

            ' add days in the first half
            For i As Integer = startDate.Day To lastDate.Day

                Dim day As String = startDate.ToString("yyyy") & startDate.ToString("MM") & i.ToString("00")

                Dim workContract As Integer

                Dim dtTimetable As DataTable = GetTimetableEmployee(empNo, exFrom, exTo, workContract)

                'check if date is holiday and work type <> 8,9
                If New BSHoliday(serverName).CheckHoliday(organNo, day) Then

                    workContract = 0

                End If

                DAWorkData.InsertWorkDate(organNo, empNo, exFrom, exTo, day, dtTimetable, workContract)

            Next

            ' get the first date in the second month
            Dim firstDate As Date = New Date(endDate.Year, endDate.Month, 1)

            ' add days in the sencond month
            For i As Integer = firstDate.Day To endDate.Day

                Dim day As String = firstDate.ToString("yyyy") & firstDate.ToString("MM") & i.ToString("00")

                Dim workContract As Integer

                Dim dtTimetable As DataTable = GetTimetableEmployee(empNo, exFrom, exTo, workContract)

                'check if date is holiday and work type <> 8,9
                If New BSHoliday(serverName).CheckHoliday(organNo, day) Then

                    workContract = 0

                End If

                DAWorkData.InsertWorkDate(organNo, empNo, exFrom, exTo, day, dtTimetable, workContract)

            Next

        End If

        Return True
        
    End Function


    ''' <summary>
    ''' Check workdate exist, return true if exist else
    ''' </summary>
    ''' <param name="organNo"></param>
    ''' <param name="empNo"></param>
    ''' <param name="startDate"></param>
    ''' <param name="endDate"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CheckWorkDateExist(ByVal organNo As String, ByVal empNo As String,
                                       ByVal startDate As DateTime, ByVal endDate As DateTime) As Boolean

        ' number of days in period of time
        Dim numOfDate As Integer = 0

        Dim dBegin As DateTime = startDate

        While dBegin <> endDate

            numOfDate += 1

            dBegin = dBegin.AddDays(1)

        End While


        ' get number of Work Date in Database if it < numOfDate then clear and insert 
        Dim dtWorkData As DataTable = GetWorkData(organNo, empNo, startDate.ToString("yyyyMMdd"), endDate.ToString("yyyyMMdd"))

        If dtWorkData.Rows.Count < numOfDate + 1 Then

            Return False

        Else

            Return True

        End If

    End Function


    ''' <summary>
    ''' update work data when user edit
    ''' </summary>
    ''' <param name="startTime"></param>
    ''' <param name="endTime"></param>
    ''' <param name="startTimeA"></param>
    ''' <param name="endTimeA"></param>
    ''' <param name="timetableNo"></param>
    ''' <param name="exFrom"></param>
    ''' <param name="exTo"></param>
    ''' <param name="restTime"></param>
    ''' <param name="organNo"></param>
    ''' <param name="empNo"></param>
    ''' <param name="workDate"></param>
    ''' <param name="workTime"></param>
    ''' <param name="overtime"></param>
    ''' <param name="lateness"></param>
    ''' <param name="leaveEarly"></param>
    ''' <param name="memo"></param>
    ''' <param name="workDays"></param>
    ''' <param name="paidVacation"></param>
    ''' <param name="specialHolidays"></param>
    ''' <param name="absenceDays"></param>
    ''' <param name="holidayDays"></param>
    ''' <param name="compensatoryDayOff"></param>
    ''' <param name="workTypeNo"></param>
    ''' <param name="workContract"></param>
    ''' <param name="holidayTime"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function Update(ByVal startTime As Object,
                           ByVal endTime As Object,
                           ByVal startTimeA As Object,
                           ByVal endTimeA As Object,
                           ByVal timetableNo As String,
                           ByVal exFrom As String,
                           ByVal exTo As String,
                           ByVal restTime As Object,
                           ByVal organNo As String,
                           ByVal empNo As String,
                           ByVal workDate As String,
                           ByVal workTime As Object,
                           ByVal overtime As Object,
                           ByVal lateness As Object,
                           ByVal leaveEarly As Object,
                           ByVal memo As Object,
                           ByVal workDays As Object,
                           ByVal paidVacation As String,
                           ByVal specialHolidays As String,
                           ByVal absenceDays As String,
                           ByVal holidayDays As String,
                           ByVal compensatoryDayOff As String,
                           ByVal workTypeNo As String,
                           ByVal workContract As Object,
                           ByVal holidayTime As Object) As Boolean


        If startTime.ToString().Equals("0") Then

            startTime = DBNull.Value

        End If

        If endTime.ToString().Equals("0") Then

            endTime = DBNull.Value

        End If

        If startTimeA.ToString().Equals("0") Then

            startTimeA = DBNull.Value

        End If

        If endTimeA.ToString().Equals("0") Then

            endTimeA = DBNull.Value

        End If

        If restTime.ToString().Equals("0") Then

            restTime = DBNull.Value

        End If

        If workTime.ToString().Equals("0") Then

            workTime = DBNull.Value

        End If

        If overtime.ToString().Equals("0") Then

            overtime = DBNull.Value

        End If

        If lateness.ToString().Equals("0") Then

            lateness = DBNull.Value

        End If

        If leaveEarly.ToString().Equals("0") Then

            leaveEarly = DBNull.Value

        End If

        If memo Is Nothing Then

            memo = DBNull.Value

        End If

        If holidayTime.ToString().Equals("0") Then

            holidayTime = DBNull.Value

        End If

        ' execute update
        Dim result As Integer = DAWorkData.UpdateWorkData(startTime, endTime, startTimeA, endTimeA, timetableNo, exFrom,
                                                exTo, restTime, organNo, empNo, workDate, workTime, overtime,
                                                lateness, leaveEarly, memo, workDays, paidVacation, specialHolidays,
                                                absenceDays, holidayDays, compensatoryDayOff, workTypeNo, workContract, holidayTime)

        If result <> 0 Then

            Return True

        Else

            Return False

        End If



    End Function

    ''' <summary>
    ''' calculate employee work data details
    ''' </summary>
    ''' <param name="dtWorkDate"></param>
    ''' <param name="contract"></param>
    ''' <param name="working"></param>
    ''' <param name="overtime"></param>
    ''' <param name="holiday"></param>
    ''' <param name="paidLeave"></param>
    ''' <param name="replace"></param>
    ''' <param name="absence"></param>
    ''' <param name="special"></param>
    ''' <param name="lateness"></param>
    ''' <param name="leaveEarly"></param>
    ''' <remarks></remarks>
    Public Sub CalculateDetailWorkData(ByVal dtWorkDate As DataTable, ByRef contract As Integer, ByRef working As Integer,
                                       ByRef overtime As Integer, ByRef holiday As Integer, ByRef paidLeave As Double,
                                       ByRef replace As Double, ByRef absence As Double, ByRef special As Double,
                                       ByRef lateness As Integer, ByRef leaveEarly As Integer)

        For Each row As DataRow In dtWorkDate.Rows


            contract += Integer.Parse(row.Item("CONTRACT_TIME"))

            If Not row.Item("WORKING_TIME").Equals(DBNull.Value) Then

                working += Integer.Parse(row.Item("WORKING_TIME"))

            End If

            If Not row.Item("OVER_TIME").Equals(DBNull.Value) Then

                overtime += Integer.Parse(row.Item("OVER_TIME"))

            End If

            If Not row.Item("HOLIDAY_TIME").Equals(DBNull.Value) Then

                holiday += Integer.Parse(row.Item("HOLIDAY_TIME"))

            End If

            If Not row.Item("PAID_VACATION_DAYS").Equals(DBNull.Value) Then

                paidLeave += Double.Parse(row.Item("PAID_VACATION_DAYS"))

            End If

            If Not row.Item("COMPENSATORY_DAY_OFF").Equals(DBNull.Value) Then

                replace += Double.Parse(row.Item("COMPENSATORY_DAY_OFF"))

            End If

            If Not row.Item("ABSENCE_DAYS").Equals(DBNull.Value) Then

                absence += Double.Parse(row.Item("ABSENCE_DAYS"))

            End If

            If Not row.Item("SPECIAL_HOLIDAYS").Equals(DBNull.Value) Then

                special += Double.Parse(row.Item("SPECIAL_HOLIDAYS"))

            End If

            If Not row.Item("BEING_LATE_TIME").Equals(DBNull.Value) Then


                lateness += 1


            End If

            If Not row.Item("LEAVING_EARLY_TIME").Equals(DBNull.Value) Then

                leaveEarly += 1

            End If

        Next


    End Sub


    ''' <summary>
    '''  Get Timetable of employee, timetable no, ex from, ex to, work contract minutes
    ''' </summary>
    ''' <param name="empNo"></param>
    ''' <param name="exFrom"></param>
    ''' <param name="exTo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetTimetableEmployee(ByVal empNo As String, ByVal exFrom As String, ByVal exTo As String,
                                         ByRef workContract As integer) As DataTable

        ' get timetable
        Dim dtTimetable As DataTable = DAWorkData.GetTimetableEmployee(empNo, exFrom, exTo)

        ' get rest time
        Dim dtRestTime As DataTable

        ' get rest times
        Dim dtoTimetable As DTOTimetable = New DTOTimetable()

        dtoTimetable.TimetableNoProperty = dtTimetable.Rows.Item(0).Item("TIME_TABLE_NO")

        dtoTimetable.ExFromProperty = dtTimetable.Rows.Item(0).Item("EXPIRATION_FROM_TIME_TABLE")

        dtoTimetable.ExToProperty = dtTimetable.Rows.Item(0).Item("EXPIRATION_TO_TIME_TABLE")

        dtRestTime = New BSTimetable(serverName).GetRestTime(dtoTimetable)

        ' calculate rest time
        Dim rest As Integer = CalculateRestTime(dtTimetable.Rows.Item(0).Item("WORK_FROM"),
                                                dtTimetable.Rows.Item(0).Item("WORK_TO"), dtRestTime)

        Dim work As Integer = Functions.SubtractHourReturnHour(dtTimetable.Rows.Item(0).Item("WORK_TO"),
                                                               dtTimetable.Rows.Item(0).Item("WORK_FROM"))

        workContract = Functions.ConvertHourToMinutes(Functions.SubtractHourReturnHour(work, rest))

        Return dtTimetable


    End Function


    ''' <summary>
    ''' Get contract minutes in 1 day
    ''' </summary>
    ''' <param name="timetableNo"></param>
    ''' <param name="exFrom">yyyyMMdd</param>
    ''' <param name="exTo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContractMinutesInDay(ByVal timetableNo As String, ByVal exFrom As String, ByVal exTo As String) As Integer

        Dim bsTimetable As BSTimetable = New BSTimetable(serverName)

        Dim dtTimetable As DataTable = bsTimetable.GetTimetable(timetableNo, exFrom, exTo)

        ' get rest times
        Dim dtoTimetable As DTOTimetable = New DTOTimetable()

        dtoTimetable.TimetableNoProperty = timetableNo

        dtoTimetable.ExFromProperty = exFrom

        dtoTimetable.ExToProperty = exTo

        Dim dtRestTime As DataTable = bsTimetable.GetRestTime(dtoTimetable)

        ' calculate rest time
        Dim rest As Integer = CalculateRestTime(dtTimetable.Rows.Item(0).Item("WORK_FROM"),
                                                dtTimetable.Rows.Item(0).Item("WORK_TO"), dtRestTime)

        Dim work As Integer = Functions.SubtractHourReturnHour(dtTimetable.Rows.Item(0).Item("WORK_TO"),
                                                               dtTimetable.Rows.Item(0).Item("WORK_FROM"))

        Return Functions.ConvertHourToMinutes(Functions.SubtractHourReturnHour(work, rest))

    End Function


    ''' <summary>
    ''' check working situation employee
    ''' </summary>
    ''' <param name="organNo">organization no</param>
    ''' <param name="empNo">employee No</param>
    ''' <param name="day">yyyyMMdd</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CheckWorkSituation(ByVal organNo As String, ByVal empNo As String, ByVal day As String) As Boolean

        If DAWorkData.CheckWorkSituation(organNo, empNo, day) > 0 Then

            Return True
            
        Else

            Return False

        End If

    End Function


End Class
